import enum
from sqlalchemy import Index
from exts import db


class Departments(db.Model):
    __tablename__ = 'departments'

    dept_no = db.Column(db.String(4), nullable=False, primary_key=True)
    dept_name = db.Column(db.String(40), nullable=False)

    __table_args__ = (
        db.UniqueConstraint('dept_name', name='dept_name'),
    )

    def to_dict(self):
        return {
            'dept_no': self.dept_no,
            'dept_name': self.dept_name
        }


class Employees(db.Model):
    __tablename__ = 'employees'

    class GenderEnum(enum.Enum):
        M = 'M'
        F = 'F'

    emp_no = db.Column(db.Integer, nullable=False, primary_key=True)
    birth_date = db.Column(db.Date, nullable=False)
    first_name = db.Column(db.String(14), nullable=False, index=True)
    last_name = db.Column(db.String(16), nullable=False)
    gender = db.Column(db.Enum(GenderEnum), nullable=False)
    hire_date = db.Column(db.Date, nullable=False)

    def to_dict(self):
        return {
            'emo_no': self.emp_no,
            'birth_date': self.birth_date.strftime('%Y-%m-%d'),
            'first_name': self.first_name,
            'last_name': self.last_name,
            'gender': self.gender.value,
            'hire_date': self.hire_date.strftime('%Y-%m-%d')
        }


class DeptEmp(db.Model):
    __tablename__ = 'dept_emp'

    emp_no = db.Column(db.Integer,
                       db.ForeignKey('employees.emp_no', ondelete='CASCADE', name='dept_emp_ibfk_1'),
                       nullable=False,
                       primary_key=True)
    dept_no = db.Column(db.String(4),
                        db.ForeignKey('departments.dept_no', ondelete='CASCADE', name='dept_emp_ibfk_2'),
                        nullable=False,
                        primary_key=True)
    from_date = db.Column(db.Date, nullable=False)
    to_date = db.Column(db.Date, nullable=False)

    emp = db.relationship('Employees', backref='dept_emp')
    dept = db.relationship('Departments', backref='dept_emp')
    __table_args__ = (
        Index('dept_no_index', 'dept_no'),
        db.UniqueConstraint('emp_no', 'dept_no', name='uq_dept_emp_emp_no_dept_no'),
    )

    def to_dict(self):
        return {
            'emp_no': self.emp_no,
            'dept_no': self.dept_no,
            'from_date': self.from_date.strftime('%Y-%m-%d'),
            'to_date': self.to_date.strftime('%Y-%m-%d')
        }


class DeptManager(db.Model):
    __tablename__ = 'dept_manager'

    emp_no = db.Column(db.Integer, db.ForeignKey('employees.emp_no', ondelete='CASCADE', name='dept_manager_ibfk_1'),
                       nullable=False,
                       primary_key=True)
    dept_no = db.Column(db.String(4),
                        db.ForeignKey('departments.dept_no', ondelete='CASCADE', name='dept_manager_ibfk_2'),
                        nullable=False,
                        primary_key=True)
    from_date = db.Column(db.Date, nullable=False)
    to_date = db.Column(db.Date, nullable=False)

    emp = db.relationship('Employees', backref='dept_manager')
    dept = db.relationship('Departments', backref='dept_manager')
    __table_args__ = (
        Index('dept_no_index', 'dept_no'),
        db.UniqueConstraint('emp_no', 'dept_no', name='uq_dept_manager_emp_no_dept_no'),
    )

    def to_dict(self):
        return {
            'emp_no': self.emp_no,
            'dept_no': self.dept_no,
            'from_date': self.from_date.strftime('%Y-%m-%d'),
            'to_date': self.to_date.strftime('%Y-%m-%d')
        }


class Titles(db.Model):
    __tablename__ = 'titles'

    emp_no = db.Column(db.Integer, db.ForeignKey('employees.emp_no', ondelete='CASCADE', name='titles_ibfk_1'),
                       nullable=False,
                       primary_key=True)
    title = db.Column(db.String(64), nullable=False, primary_key=True)
    from_date = db.Column(db.Date, nullable=False, primary_key=True)
    to_date = db.Column(db.Date, nullable=True)

    __table_args__ = (
        db.UniqueConstraint('emp_no', 'title', 'from_date', name='uq_titles_emp_no_title_from_date'),
    )

    def to_dict(self):
        return {
            'emp_no': self.emp_no,
            'title': self.title,
            'from_date': self.from_date.strftime('%Y-%m-%d'),
            'to_date': self.to_date.strftime('%Y-%m-%d')
        }


class DeptManagerTitle(db.Model):
    __tablename__ = 'dept_manager_title'

    emp_no = db.Column(db.Integer, nullable=False, primary_key=True)
    from_date = db.Column(db.Date, nullable=False)
    to_date = db.Column(db.Date, nullable=True)

    def to_dict(self):
        return {
            'emp_no': self.emp_no,
            'from_date': self.from_date.strftime('%Y-%m-%d'),
            'to_date': self.to_date.strftime('%Y-%m-%d')
        }
